package com.habdqn.filght.information.dao.impl;

import com.habdqn.filght.information.dao.BaseDao;
import com.habdqn.filght.information.dao.FlightDao;
import com.habdqn.filght.information.entity.Airport;
import com.habdqn.filght.information.entity.Flight;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author Xing.Jiejian
 * @date 2020/11/23
 */
public class FlightDaoImpl extends BaseDao implements FlightDao {
    @Override
    public List<Flight> find(Date date, String takeCityName, String landingCityName) {
        StringBuilder sb = new StringBuilder("SELECT f.id, f.code, f.date, f.airline, f.type, f.take_airport_id,")
                .append(" f.landing_airport_id,f.take_time, f.landing_time, f.stop_airport, f.flight_time, ")
                .append(" f.reference_price,ta.name taName, ta.city_name taCityName,la.name laName, ")
                .append(" la.city_name laCityName FROM flight f inner JOIN airport ta ON f.take_airport_id=ta.id")
                .append(" inner JOIN airport la ON f.landing_airport_id=la.id WHERE 1=1 ");
        List<Object> paramList = new ArrayList<>();
        if (date!=null){
            sb.append(" AND f.date=? ");
            paramList.add(date);
        }
        if (takeCityName!=null&&!"".equals(takeCityName)){
            sb.append(" AND ta.city_name=? ");
            paramList.add(takeCityName.trim());
        }
        if (landingCityName!=null&&!"".equals(landingCityName)){
            sb.append(" AND la.city_name=? ");
            paramList.add(landingCityName.trim());
        }
        try (ResultSet rs = executeQuery(sb.toString(),paramList.toArray())){
            List<Flight> flights = new ArrayList<>();
            while (rs.next()){
                // 组装航班信息
                Flight flight = new Flight();
                flight.setId(rs.getInt("id"));
                flight.setCode(rs.getString("code"));
                flight.setDate(rs.getDate("date"));
                flight.setAirline(rs.getString("airline"));
                flight.setType(rs.getString("type"));
                flight.setTakeAirportId(rs.getInt("take_airport_id"));
                flight.setTakeTime(rs.getTime("take_time"));
                flight.setLandingAirportId(rs.getInt("landing_airport_id"));
                flight.setLandingTime(rs.getTime("landing_time"));
                flight.setStopAirport(rs.getString("stop_airport"));
                flight.setFlightTime(rs.getString("flight_time"));
                flight.setReferencePrice(rs.getFloat("reference_price"));
                // 组装起飞机场信息
                Airport takeAirport = new Airport(flight.getTakeAirportId());
                takeAirport.setName(rs.getString("taName"));
                takeAirport.setCityName(rs.getString("taCityName"));
                flight.setTakeAirport(takeAirport);
                // 组装到达机场信息
                Airport landingAirport = new Airport(flight.getLandingAirportId());
                landingAirport.setName(rs.getString("laName"));
                landingAirport.setCityName(rs.getString("laCityName"));
                flight.setLandingAirport(landingAirport);
                // 航班加入集合
                flights.add(flight);
            }
            return flights;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }
}
